package com.edu.publisher.mapper;

import com.edu.publisher.bean.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @ClassName: TrafficStatsMapper
 * @Author: wqz
 * @Data: 2022/10/21 0:04
 * @Description: 流量域统计mapper接口
 */
public interface TrafficStatsMapper {
    // 1. 获取某一天各个渠道独立访客数
    @Select("select ch,sum(uv_ct) uv_ct from dws_traffic_vc_ch_ar_is_new_page_view_window where " +
            " toYYYYMMDD(stt)=#{date} group by ch having uv_ct > 0 order by uv_ct desc limit #{limit}")
    List<TrafficUvCt> selectChUvCt(@Param("date") Integer date, @Param("limit") Integer limit);

    // 2. 获取各渠道会话数
    @Select("select ch,sum(sv_ct) sv_ct from dws_traffic_vc_ch_ar_is_new_page_view_window where " +
            " toYYYYMMDD(stt)=#{date} group by ch having sv_ct > 0 order by sv_ct desc limit #{limit}")
    List<TrafficSvCt> selectChSvCt(@Param("date") Integer date, @Param("limit") Integer limit);

    // 3. 获取各渠道会话平均页面浏览数
    @Select("select ch,cast(sum(pv_ct) / sum(sv_ct) as decimal(38,2)) pv_per_session from dws_traffic_vc_ch_ar_is_new_page_view_window where " +
            " toYYYYMMDD(stt)=#{date} group by ch having pv_per_session > 0 order by pv_per_session desc limit #{limit}")
    List<TrafficPvPerSession> selectChPvPerSession(@Param("date") Integer date, @Param("limit") Integer limit);

    // 4. 获取各渠道会话平均页面访问时长
    @Select("select ch,cast(sum(dur_sum) / sum(sv_ct) / (1000 * 60 * 60) as decimal(38,2)) dur_per_session from dws_traffic_vc_ch_ar_is_new_page_view_window where " +
            " toYYYYMMDD(stt)=#{date} group by ch having dur_per_session > 0 order by dur_per_session desc limit #{limit}")
    List<TrafficDurPerSession> selectChDurPerSession(@Param("date") Integer date, @Param("limit") Integer limit);


    // 5. 获取各渠道跳出率
    @Select("select ch,cast(sum(uj_ct) / sum(sv_ct) as decimal(38,2)) uj_rate from dws_traffic_vc_ch_ar_is_new_page_view_window where " +
            " toYYYYMMDD(stt)=#{date} group by ch having uj_rate > 0 order by uj_rate limit #{limit}")
    List<TrafficUjRate> selectChUjTate(@Param("date") Integer date, @Param("limit") Integer limit);

    // 6. 分时流量数据查询
    @Select("select toHour(stt) hr, sum(uv_ct), sum(pv_ct), cast(sum(pv_ct) / sum(sv_ct) as decimal(38,2)) pv_per_session from dws_traffic_vc_ch_ar_is_new_page_view_window where " + "toYYYYMMDD(stt)=#{date} group by hr")
    List<TrafficVisitorStatsPerHour> selectVisitorStatsPerHr(@Param("date") Integer date);
}
